/**
    2018年3月24日 星期六
    获取当前财务状态
    call get__curfnc_status(2)
**/

drop procedure if exists `get__curfnc_status`; -- 同同时执行报错
delimiter //
create procedure `get__curfnc_status`(
    v_uid   int
)
this_sp:begin
    -- 需要输出资金
    declare v_num int default 0;                        -- 财务总条数统计
    declare v_surplus numeric(12, 3) default 0;         -- 总资金盈余
    declare v_annual_num int default 0;                 -- 年度财务数
    declare v_annual_surplus numeric(12, 3) default 0;  -- 年度财务盈余
    declare v_month_num int default 0;                  -- 月度财务数
    declare v_month_surplus numeric(12, 3) default 0;   -- 月度财务盈余   
    declare v_daily_num int default 0;                  -- 今日财务数
    declare v_daily_surplus numeric(12, 3) default 0;   -- 今日财务盈余     
    declare v_lastd_num int default 0;                  -- 前日财务数
    declare v_lastd_surplus numeric(12, 3) default 0;   -- 前日财务盈余    

    -- 非输出变量
    declare v_cdt date default CURDATE();
    -- 用户id为空时返回
    if v_uid is null then
        leave this_sp;
    end if;

    -- :) 总资金盈余
    select count(1) into v_num from `fnc1000c` where `uid` = v_uid;

    select 
        (
            select ifnull(sum(a1.`money`), 0) from `fnc1000c` a1 
            left join `fnc0020c` a2 on a1.`slave_id`=a2.`listid` 
            where a1.`uid`= v_uid and a1.`type`='IN' and
                (a1.`slave_id` is null or a2.`type`<>'M0')
        )
        - 
        (
            select ifnull(sum(b1.`money`), 0) from `fnc1000c` b1 
            left join `fnc0020c` b2 on b1.`slave_id`=b2.`listid` 
            where b1.`uid`= v_uid and b1.`type`='OU' and
                (b1.`slave_id` is null or b2.`type`<>'M0')
        )
        into v_surplus
        ;

    -- :) 年度财务数/盈余
    select count(1) into v_annual_num from `fnc1000c` where `uid`=v_uid and year(`date`) = year(v_cdt);
    select 
        (
            select ifnull(sum(a1.`money`), 0) from `fnc1000c` a1 
            left join `fnc0020c` a2 on a1.`slave_id`=a2.`listid` 
            where a1.`uid`= v_uid and a1.`type`='IN' and year(a1.`date`) = year(v_cdt) and
                (a1.`slave_id` is null or a2.`type`<>'M0')
        )
        - 
        (
            select ifnull(sum(b1.`money`), 0) from `fnc1000c` b1 
            left join `fnc0020c` b2 on b1.`slave_id`=b2.`listid` 
            where b1.`uid`= v_uid and b1.`type`='OU'  and year(b1.`date`) = year(v_cdt) and 
                (b1.`slave_id` is null or b2.`type`<>'M0')
        )
        into v_annual_surplus
        ;

    -- :) 月度财务数/盈余
    select count(1) into v_month_num from `fnc1000c` where `uid`=v_uid and date_format(`date`, '%Y-%m') = date_format(v_cdt, '%Y-%m');
    select 
        (
            select ifnull(sum(a1.`money`), 0) from `fnc1000c` a1 
            left join `fnc0020c` a2 on a1.`slave_id`=a2.`listid` 
            where a1.`uid`= v_uid and a1.`type`='IN' and date_format(a1.`date`, '%Y-%m') = date_format(v_cdt, '%Y-%m') and
                (a1.`slave_id` is null or a2.`type`<>'M0')
        )
        - 
        (
            select ifnull(sum(b1.`money`), 0) from `fnc1000c` b1 
            left join `fnc0020c` b2 on b1.`slave_id`=b2.`listid` 
            where b1.`uid`= v_uid and b1.`type`='OU'  and date_format(b1.`date`, '%Y-%m') = date_format(v_cdt, '%Y-%m') and 
                (b1.`slave_id` is null or b2.`type`<>'M0')
        )
        into v_month_surplus
        ;        

    -- :) 今日财务数/盈余
    select count(1) into v_daily_num from `fnc1000c` where `uid`=v_uid and `date` = v_cdt;
    select 
        (
            select ifnull(sum(a1.`money`), 0) from `fnc1000c` a1 
            left join `fnc0020c` a2 on a1.`slave_id`=a2.`listid` 
            where a1.`uid`= v_uid and a1.`type`='IN' and a1.`date` = v_cdt and
                (a1.`slave_id` is null or a2.`type`<>'M0')
        )
        - 
        (
            select ifnull(sum(b1.`money`), 0) from `fnc1000c` b1 
            left join `fnc0020c` b2 on b1.`slave_id`=b2.`listid` 
            where b1.`uid`= v_uid and b1.`type`='OU'  and b1.`date` = v_cdt and 
                (b1.`slave_id` is null or b2.`type`<>'M0')
        )
        into v_daily_surplus
        ;     
    
    -- :) 前日财务数/盈余
    select count(1) into v_lastd_num from `fnc1000c` where `uid`=v_uid and `date` = v_cdt - 1;
    select 
        (
            select ifnull(sum(a1.`money`), 0) from `fnc1000c` a1 
            left join `fnc0020c` a2 on a1.`slave_id`=a2.`listid` 
            where a1.`uid`= v_uid and a1.`type`='IN' and a1.`date` = v_cdt - 1 and
                (a1.`slave_id` is null or a2.`type`<>'M0')
        )
        - 
        (
            select ifnull(sum(b1.`money`), 0) from `fnc1000c` b1 
            left join `fnc0020c` b2 on b1.`slave_id`=b2.`listid` 
            where b1.`uid`= v_uid and b1.`type`='OU'  and b1.`date` = v_cdt - 1 and 
                (b1.`slave_id` is null or b2.`type`<>'M0')
        )
        into v_lastd_surplus
        ;     

    -- 输出查询值
    select
        v_num as `num`,
        v_surplus as `surplus`,
        v_annual_num as `annual_num`,
        v_annual_surplus as `annual_sps`,
        v_month_num as `month_num`,
        v_month_surplus as `month_sps`,
        v_daily_num as `daily_num`,
        v_daily_surplus as `daily_sps`,
        v_lastd_num as `lastd_num`,
        v_lastd_surplus as `lastd_sps`
        ;        
end;
